package com.liaoyin.travel.util;

import com.liaoyin.travel.exception.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.PostConstruct;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Excel导出工具类
 *
 * @author Kuang.JiaZhuo
 * @date 2020-03-03 15:41
 */
@Component
public class ExcelUtil {

    /**
     * 服务器文件地址
     */
    @Value("${gate.file.realPath}${gate.file.excelPath}")
    private String serverExcelAddress;

    /**
     * 服务器访问地址
     */
    @Value("${gate.file.server}${gate.file.excelPath}")
    private String serverExcelUrl;

    public static ExcelUtil excelUtil;

    /**
     * @方法名：isAllRowEmpty
     * @描述： 判断是否整行都为空
     * @作者： kjz
     * @日期： Created in 2020/4/3 12:03
     */
    public static boolean isAllRowEmpty(Row row, int headingLength) {
        boolean result = true;
        for(int j=0;j<headingLength;j++){
            Cell cell = row.getCell(j);
            String cellValue = ExcelUtil.getCellValue(cell);
            if(StringUtil.isNotBlank(cellValue)){
                result = false;
                break;
            }
        }
        return result;
    }

    /**
     * @方法名：getCellValue
     * @描述： 获取单元格的值
     * @作者： kjz
     * @日期： Created in 2020/4/3 12:03
     */
    public static String getCellValue(Cell cell) {
        if(cell == null){
            return null;
        }
        String cellValue = "";
//        System.err.println("单元格类型："+cell.getCellTypeEnum());
        switch (cell.getCellTypeEnum()) {
            case STRING: //字符串类型
                cellValue= cell.getStringCellValue().trim();
                break;
            case BOOLEAN:  //布尔类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC: //数值类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
                    cellValue =   DateUtil.format(cell.getDateCellValue(),"yyyy-MM-dd");
                } else {  //否
                    cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                }
                break;
            default: //其它类型，取空串吧
                cellValue = "";
                break;
        }

        return cellValue;
    }

    @PostConstruct
    public void init() {
        excelUtil = this;
    }

    /**
     * @Describe excel最完美的导出
     * @Author kuang.jia.zhuo
     * @param obj 导出的数据
     * @param key 标题和数据的对应Map
     * @param bookName 工作簿名
     * @param pageSize 每个sheet的最大数据量
     */
    public static String excelDataList(List<Object> obj, Map<String, String> key, String bookName, int pageSize) {

        //判断标题和字段的对应map不能为空
        if(key==null||key.size()<=0){
            throw new BusinessException("headline.is.null");
        }
        String[] param=new String[key.size()];

        //工作簿
        Workbook workbook= new HSSFWorkbook();
        //设置字体
        HSSFFont font = (HSSFFont) workbook.createFont();
        font.setFontHeightInPoints((short) 20); //字体高度
        font.setColor(HSSFFont.COLOR_RED); //字体颜色
        font.setFontName("黑体"); //字体
        font.setItalic(true); //是否使用斜体
        //设置单元格类型
        HSSFCellStyle cellStyle = (HSSFCellStyle) workbook.createCellStyle();
        cellStyle.setFont(font);
//                 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
        cellStyle.setWrapText(true);

        obj = (List<Object>) obj.get(0);
        System.err.println("obj="+obj);
        System.err.println("obj.size()="+obj.size());
        //考虑分页
        int pages = 1;
        List<List<Object>> result = new ArrayList<>();
        if(pageSize==0 || pageSize < 0){
                result.add(obj);
        }else{
            pages = obj.size() / pageSize;
            if(obj.size() % pageSize > 0){
                pages += 1;
            }
            System.err.println("pages="+pages);
            if(pages==1){
                result.add(obj);
            }else if(pages>1){
                int priIndex = 0;
                int lastIndex = 0;
                for(int i = 0;i<pages;i++){
                    priIndex = pageSize * i;
                    lastIndex = priIndex + pageSize;
                    if(i==pages-1){
                        result.add(obj.subList(priIndex, obj.size()));
                    }else{
                        result.add(obj.subList(priIndex, lastIndex));
                    }
                }
            }
        }
        System.err.println("result="+result);
        System.err.println("pages="+pages);
        for (int i = 0; i < pages; i++) {
            System.err.println("97--执行");
            Sheet sheet = workbook.createSheet("第"+(i+1)+"页");
            Row row = null;
            row=sheet.createRow(0);//创建第一行
            Set set=key.entrySet();
            Iterator iterator=set.iterator();
            int   cellIndex=0;
            while(iterator.hasNext()){
                Map.Entry<String, String> enter=(Map.Entry<String, String>)
                        iterator.next();
                row.createCell(cellIndex).setCellValue(enter.getValue());
                param[cellIndex]=enter.getKey();
                cellIndex++;
            }
            obj = (List<Object>) result.get(i);
            System.err.println("obj="+obj);
            for (int j=0;j<obj.size();j++){
                Object o = obj.get(j);
                row = sheet.createRow(j + 1);
                for (int k=0;k<param.length;k++){
                    Object value = Trans2BO.invokeGetMethod(o, param[k], null);
//                    System.err.println("value="+value);
                    if(value==null){
                        row.createCell(k).setCellValue("");
                    }else{
                        row.createCell(k).setCellValue(value.toString());
                    }
                }
            }
            for (int k = 0; k < param.length; k++) {
                sheet.autoSizeColumn(k);
            }
        }

        /** 上传到服务器 */
        bookName = bookName + "-"+DateUtil.getCurrentyyyymmddHHmmss()+".xls";
        String uploadUrl = excelUtil.serverExcelAddress+bookName;
//        System.err.println(uploadUrl);
        try {
            FileOutputStream output = new FileOutputStream(uploadUrl);
            workbook.write(output);
            workbook.close();
            output.flush();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /** 返回全路径url */
        String fileUrl = excelUtil.serverExcelUrl+bookName;
        return fileUrl;

    }

    /**
     * @方法名：createWorkBook
     * @描述： 解析excel生成workbook
     * @作者： kjz
     * @日期： Created in 2020/3/5 22:26
     */
    public static Workbook createWorkBook(String fileName, MultipartFile file){
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new BusinessException("file.format.error");
        }

        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        Workbook wb = null;
        try {
            InputStream is = file.getInputStream();
            wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 获取文件夹下所有文件的名称 + 模糊查询（当不需要模糊查询时，queryStr传空或null即可）
     * 1.当路径不存在时，map返回retType值为1
     * 2.当路径为文件路径时，map返回retType值为2，文件名fileName值为文件名
     * 3.当路径下有文件夹时，map返回retType值为3，文件名列表fileNameList，文件夹名列表folderNameList
     * @param folderPath 路径
     * @param queryStr 模糊查询字符串
     * @return
     */
    public static HashMap<String, Object> getFilesName(String folderPath , String queryStr) {
        HashMap<String, Object> map = new HashMap<>();
        List<String> fileNameList = new ArrayList<>();//文件名列表
        List<String> folderNameList = new ArrayList<>();//文件夹名列表
        File f = new File(folderPath);
        if (!f.exists()) { //路径不存在
            map.put("retType", "1");
        }else{
            boolean flag = f.isDirectory();
            if(flag==false){ //路径为文件
                map.put("retType", "2");
                map.put("fileName", f.getName());
            }else{ //路径为文件夹
                map.put("retType", "3");
                File fa[] = f.listFiles();
                queryStr = queryStr==null ? "" : queryStr;//若queryStr传入为null,则替换为空（indexOf匹配值不能为null）
                for (int i = 0; i < fa.length; i++) {
                    File fs = fa[i];
                    if(fs.getName().indexOf(queryStr)!=-1){
                        if (fs.isDirectory()) {
                            folderNameList.add(fs.getName());
                        } else {
                            fileNameList.add(fs.getName());
                        }
                    }
                }
                map.put("fileNameList", fileNameList);
                map.put("folderNameList", folderNameList);
            }
        }
        return map;
    }


    /**
     * @方法名：deleteExcelByAll
     * @描述： 删除所有的excel文件
     * @作者： kjz
     * @日期： Created in 2020/3/4 3:19
     */
    public static void deleteExcelByAll(){
        HashMap<String,Object> filesName = getFilesName(excelUtil.serverExcelAddress, null);
        System.err.println(filesName);
        if(filesName.get("retType").equals("3")){
           List<String> list = (List<String> )filesName.get("fileNameList");
           if(list.size()>0){
               for (String str : list) {
                   File file = new File(excelUtil.serverExcelAddress+str);
                   file.delete();
               }
           }
        }
    }




}
